package com.ls.dao.user;

import com.ls.dao.BaseDao;
import com.ls.pojo.Role;
import com.ls.pojo.User;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李朔
 * @version 1.0
 */
public class UserDaoImpl implements UserDao{
    //得到要登陆的用户
    public User getLoginUser(Connection connection, String userCode) {
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        User user = null;
        if (connection != null) {
            String sql = "select * from smbms_user where userCode=?";
            Object[] params = {userCode};
            try {
                resultSet = BaseDao.executeQuery(connection, sql, params, pstm, resultSet);

                if(resultSet.next()){
                    user=new User();
                    user.setId(resultSet.getInt("id"));
                    user.setUserCode(resultSet.getString("userCode"));
                    user.setUserName(resultSet.getString("userName"));
                    user.setUserPassword(resultSet.getString("userPassword"));
                    user.setGender(resultSet.getInt("gender"));
                    user.setBirthday(resultSet.getDate("birthday"));
                    user.setPhone(resultSet.getString("phone"));
                    user.setAddress(resultSet.getString("address"));
                    user.setUserRole(resultSet.getInt("userRole"));
                    user.setCreatedBy(resultSet.getInt("createdBy"));
                    user.setCreationDate(resultSet.getTimestamp("creationDate"));
                    user.setModifyBy(resultSet.getInt("modifyBy"));
                    user.setModifyDate(resultSet.getTimestamp("modifyDate"));
                }
                BaseDao.closeResource(null,pstm,resultSet);

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }
    //修改当前用户密码
    public int updatePwd(Connection connection,int id,String password) throws SQLException {

        PreparedStatement pstm=null;
        int row=0;
        if(connection!=null) {
            String sql = "update smbms_user set userPassword=? where id=?";
            Object[] params = {password, id};
            row = BaseDao.executeUpdate(connection, sql, params, pstm);
            BaseDao.closeResource(null, pstm, null);
        }
        return row;
    }

    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        int count=0;
        if(connection!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole=r.id");
            ArrayList<Object> list = new ArrayList<Object>();
            if(username!=null){
                sql.append(" and u.userName like ?");
                list.add("%"+username+"%");//index:0
            }
            if(userRole>0){
                sql.append(" and u.userRole=?");
                list.add(userRole);//index:1
            }
            //怎么把List转换成数组
            Object[] objects=list.toArray();
            System.out.println("userDaoImpl->getUserCount:"+sql.toString());//输出最后完整的SQL语句
             resultSet= BaseDao.executeQuery(connection, sql.toString(), objects, preparedStatement, resultSet);
             if(resultSet.next()){
                 count = resultSet.getInt("count");//从结果集中获取最终的数量
             }
             BaseDao.closeResource(null,null,resultSet);
        }
        return count;
    }


    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)
            throws Exception {
        // TODO Auto-generated method stub
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
            List<Object> list = new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%"+userName+"%");
            }
            if(userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            System.out.println("sql ----> " + sql.toString());
            rs = BaseDao.executeQuery(connection,sql.toString(),params,pstm,rs);
            while(rs.next()){
                User _user = new User();
                _user.setId(rs.getInt("id"));
                _user.setUserCode(rs.getString("userCode"));
                _user.setUserName(rs.getString("userName"));
                _user.setGender(rs.getInt("gender"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setUserRole(rs.getInt("userRole"));
                _user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(_user);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return userList;
    }

    public boolean addUser(Connection connection, User user) throws SQLException {
        PreparedStatement preparedStatement=null;
        if(connection!=null){
            //已经连接到数据库了
            String sql = "insert into smbms_user (userCode,userName,userPassword," +
                    "userRole,gender,birthday,phone,address,creationDate,createdBy) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params={user.getUserCode(),user.getUserName(),user.getUserPassword(),
            user.getUserRole(),user.getGender(),user.getBirthday(),user.getPhone(),
            user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
            int row = BaseDao.executeUpdate(connection, sql, params, preparedStatement);
            BaseDao.closeResource(null,preparedStatement,null);
            if(row>=1){
                //修改成功
                return true;
            }else{
                return false;
            }
        }
        return false;
    }
}
